package hamsterpolice.todo.data;

import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class ToDoListTable {
	
	/**
	 * Hidden constructor. Only static methods on this class.
	 */
	private ToDoListTable(){}
	
	private static final String TAG = "TodoListTable";
	public static final String TABLE_NAME = "toDoList";
	
	public static final String COLUMN_ID = "_id";
	public static final String COLUMN_USER_ID = "user_id";
	public static final String COLUMN_CATEGORY_ID = "category_id";	
	public static final String COLUMN_NAME = "name";
	public static final String COLUMN_DESCRIPTION = "desc";
	public static final String COLUMN_LOCATION = "location";
	public static final String COLUMN_LATITUDE= "latitude";
	public static final String COLUMN_LONGITUDE= "longitude";
	public static final String COLUMN_DUE_DATE= "due_date";
	public static final String COLUMN_COMPLETED= "completed";
	
	private static final String TABLE_CREATE = 
			" CREATE TABLE " + TABLE_NAME + 
			" ( " + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ COLUMN_USER_ID + " INT NOT NULL, "
			+ COLUMN_CATEGORY_ID + " INT NULL, "
			+ COLUMN_NAME + " TEXT COLLATE NOCASE NOT NULL, "
			+ COLUMN_DESCRIPTION + " TEXT COLLATE NOCASE NULL, "
			+ COLUMN_LOCATION + " TEXT COLLATE NOCASE NULL, "
			+ COLUMN_LONGITUDE + " REAL NULL, "
			+ COLUMN_LATITUDE + " REAL NULL, "
			+ COLUMN_DUE_DATE + " INT NULL, "
			+ COLUMN_COMPLETED + " INT NOT NULL DEFAULT 0, "
			+ "FOREIGN KEY(" + COLUMN_USER_ID + ") REFERENCES " + UserTable.TABLE_NAME + "(" + UserTable.COLUMN_ID + ") ON UPDATE CASCADE ON DELETE RESTRICT, " 
			+ "FOREIGN KEY(" + COLUMN_CATEGORY_ID + ") REFERENCES " + CategoryTable.TABLE_NAME + "(" + CategoryTable.COLUMN_ID + ") ON UPDATE CASCADE ON DELETE SET NULL )";
	/**
	 * Creates the user table
	 * @param db
	 */
	static void create(SQLiteDatabase db) {
		Log.i(TAG, "Creating table " + TABLE_NAME);
		db.execSQL(TABLE_CREATE);
	}

	/**
	 * Upgrades the user table
	 * @param db
	 * @param oldVersion
	 * @param newVersion
	 */
	static void upgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if(oldVersion<4){
			db.execSQL("DROP TABLE " + TABLE_NAME);
			create(db);
		}
	}
	
}
